﻿using SunshineAirlines.UserControls;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlines
{
    public partial class Search_Flights : Form
    {
        private string sqltmp;
        private DataTable dttmp;
        private int last_top;

        private static string departmentAirport, arrivalAirport;

        public Search_Flights()
        {
            InitializeComponent();
        }

        private void Search_Flights_Load(object sender, EventArgs e)
        {
            dateDEP.MinDate = DateTime.Now;

            BindComboBoxData();
            cbFLT.SelectedIndex = 0;

            // test conditions 
            cbDEPct.SelectedIndex = 4; cbARRct.SelectedIndex = 12;
            dateDEP.Value = Convert.ToDateTime("2021-07-01");

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            departmentAirport = cbDEPct.SelectedValue.ToString(); arrivalAirport = cbARRct.SelectedValue.ToString();
            if (cbDEPct.SelectedItem == null || string.IsNullOrEmpty(departmentAirport)) { noSuchFlights(true); return; }
            if (cbARRct.SelectedItem == null || string.IsNullOrEmpty(arrivalAirport)) { noSuchFlights(true); return; }

            initPanelFLT();
            DataTable directFlights, transferFlights;
            if (cbFLT.SelectedIndex != 2 && searchFlights_Direct(out directFlights)) loadUserControl(directFlights, pFLTrs);
            if (cbFLT.SelectedIndex != 1 && searchFlights_Transfer(out transferFlights)) loadUserControl(transferFlights, pFLTrs);

            if (rdoRoundWay.Checked)
            {
                initPanelRTN();
                departmentAirport = cbARRct.SelectedValue.ToString(); arrivalAirport = cbDEPct.SelectedValue.ToString();
                directFlights = transferFlights = new DataTable();
                if (searchFlights_Direct(out directFlights)) loadUserControl(directFlights, pRTNrs);
                if (searchFlights_Transfer(out transferFlights)) loadUserControl(transferFlights, pRTNrs);
            }
            noSuchFlights(false);
        }

        private void dateDEP_ValueChanged(object sender, EventArgs e)
        {
            dateRTN.MinDate = dateDEP.Value;
        }

        private void Search_Flights_SizeChanged(object sender, EventArgs e)
        {
            resizeControls();
        }

        private void rdoOneWay_CheckedChanged(object sender, EventArgs e)
        {
            label9.Visible = pRTNrs.Visible = false;
        }

        private void rdoRoundWay_CheckedChanged(object sender, EventArgs e)
        {
            label9.Visible = pRTNrs.Visible = true;
        }

        #region Query

        private bool searchFlights_Direct(out DataTable dtout)
        {
            sqltmp = @"
            select df.RouteId RouteId1,
            DepartureAirportIATA,DepartureCityCode,DepartureCityName,ArrivalAirportIATA,ArrivalCityCode,ArrivalCityName,
            s.EconomyPrice s1_EconomyPrice,s.EconomyPrice*1.25 s1_BusinessPrice,s.EconomyPrice*1.5 s1_FirstPrice,
            s.ScheduleId s1_ScheduleId,s.AircraftId s1_AircraftId,s.FlightNumber s1_FlightNumber,
            s.DepartureDate s1_DepartureDate,s.ReturnDate s1_ReturnDate,0 walkMinutes,DATEDIFF(MINUTE,DepartureDate,ReturnDate) totalMinutes
            from ( select * from RouteWithCity where DepartureAirportIATA in ({0}) and ArrivalAirportIATA in ({1}) ) df 
            left join ScheduleWithDateTime s on s.RouteId=df.RouteId
            where DepartureDate>='{2}' {3} ";
            sqltmp = string.Format(sqltmp, departmentAirport, arrivalAirport, dateDEP.Value.ToString("yyyy-MM-dd"),
               (rdoRoundWay.Checked && dateRTN.Value > DateTime.MinValue) ? string.Format(" and ReturnDate<='{0}' order by ReturnDate ", dateRTN.Value.ToString("yyyy-MM-dd")) : " order by DepartureDate ");
            dtout = DBHelper.ExecuteTable(sqltmp);
            return dtout.Rows.Count > 0;
        }

        private bool searchFlights_Transfer(out DataTable dtout)
        {
            sqltmp = " select * from TransferFlights ";
            sqltmp += string.Format(" where DepartureAirportIATA in ({0}) and ArrivalAirportIATA in ({1}) and s1_DepartureDate>='{2}' {3} ",
                departmentAirport, arrivalAirport, dateDEP.Value.ToString("yyyy-MM-dd"),
               (rdoRoundWay.Checked && dateRTN.Value > DateTime.MinValue) ? string.Format(" and s2_ReturnDate<='{0}' order by s2_ReturnDate ", dateRTN.Value.ToString("yyyy-MM-dd")) : " order by s1_DepartureDate ");
            dtout = DBHelper.ExecuteTable(sqltmp);
            return dtout.Rows.Count > 0;
        }

        private int GetRemainingTickets(int scheduleId)
        {
            int cabinTypeId = Convert.ToInt32(cbCabin.SelectedValue);
            sqltmp = string.Format(" select remaining from SoldTickets where ScheduleId={0} and CabinTypeId={1} ", scheduleId, cabinTypeId);
            return DBHelper.ExecuteScalar(sqltmp) == null ? 0 : Convert.ToInt32(DBHelper.ExecuteScalar(sqltmp));
        }
        private double GetDelayRateByRId(int routeId)
        {
            sqltmp = @" With Src as(
	                        select s.ScheduleId,DepartureDate,ReturnDate,ActualArrivalTime,DATEDIFF(MINUTE,ReturnDate,ActualArrivalTime) as TS
	                        from ScheduleWithDateTime s join FlightStatus fs on s.ScheduleId=fs.ScheduleId
	                        where s.RouteId={0} and  DATEDIFF(DAY,ActualArrivalTime,GETDATE()) between 0 and 30
                        )select case COUNT(0) when 0 then 0 else 1-(CONVERT(float,(select count(0) from Src where TS>15))/CONVERT(float,COUNT(0))) end from Src ";
            sqltmp = string.Format(sqltmp, routeId);
            return DBHelper.ExecuteScalar(sqltmp) == null ? 0f : Convert.ToDouble(DBHelper.ExecuteScalar(sqltmp));
        }

        #endregion

        #region Tools
        private void BindComboBoxData()
        {
            sqltmp = "select CityName,stuff((select ','+char(39)+IATACode+char(39) from Airport a where c.CityCode=a.CityCode for xml path('')),1,1,'') as IATAs from City c order by CityName";
            dttmp = DBHelper.ExecuteTable(sqltmp);
            if (dttmp.Rows.Count > 0)
            {
                CommonHelper.BindComboBoxList(cbDEPct, dttmp, "IATAs", "CityName");
                CommonHelper.BindComboBoxList(cbARRct, dttmp.Copy(), "IATAs", "CityName");
            }
            sqltmp = "select CabinTypeId,CabinTypeName from CabinType";
            dttmp = DBHelper.ExecuteTable(sqltmp);
            if (dttmp.Rows.Count > 0) CommonHelper.BindComboBoxList(cbCabin, dttmp, "CabinTypeId", "CabinTypeName");
        }

        private void loadUserControl(DataTable dt, Panel panel)
        {
            bool isTransfer = dt.Columns.Contains("RouteId2");

            foreach (DataRow row in dt.Rows)
            {
                flightNonStop uc_n = new flightNonStop();
                uc_n.BorderStyle = BorderStyle.Fixed3D;
                int calcLeft = (int)((double)(panel.Width - uc_n.Width) / 2);
                if (last_top == 0) last_top = 50;
                uc_n.Location = new Point(calcLeft, last_top);
                flightStop uc_1 = new flightStop();
                uc_1.BorderStyle = BorderStyle.Fixed3D;
                uc_1.Location = new Point(calcLeft, last_top);

                if (!isTransfer)   // Non-Stop
                {
                    uc_n.DepCity = row["DepartureCityName"].ToString() + "/" + row["DepartureAirportIATA"].ToString();
                    uc_n.DepTime = row["s1_DepartureDate"].ToString();
                    uc_n.ArrCity = row["ArrivalCityName"].ToString() + "/" + row["ArrivalAirportIATA"].ToString();
                    uc_n.ArrTime = row["s1_ReturnDate"].ToString();
                    uc_n.FltNum = row["s1_FlightNumber"].ToString();
                    uc_n.TotalTime = Convert.ToInt32(row["totalMinutes"].ToString());
                    uc_n.CabinType = cbCabin.Text;
                    if (cbCabin.SelectedValue.Equals(1)) uc_n.Price = Convert.ToDouble(row["s1_EconomyPrice"].ToString());
                    else if (cbCabin.SelectedValue.Equals(2)) uc_n.Price = Convert.ToDouble(row["s1_BusinessPrice"].ToString());
                    else if (cbCabin.SelectedValue.Equals(3)) uc_n.Price = Convert.ToDouble(row["s1_FirstPrice"].ToString());
                    else uc_n.Price = 0f;
                    uc_n.AvailableTickets = GetRemainingTickets(Convert.ToInt32(row["s1_ScheduleId"].ToString()));
                    uc_n.DelayRate = GetDelayRateByRId(Convert.ToInt32(row["RouteId1"].ToString()));

                    panel.Controls.Add(uc_n);
                    last_top += 150;
                }
                else   // 1-Stop
                {
                    uc_1.DepCity = row["DepartureCityName"].ToString() + "/" + row["DepartureAirportIATA"].ToString();
                    uc_1.DepTime = row["s1_DepartureDate"].ToString();
                    uc_1.TsfCity = row["TransferCityName"].ToString() + "/" + row["TransferAirportIATA"].ToString();
                    uc_1.TsfTime_1 = row["s1_ReturnDate"].ToString();
                    uc_1.TsfTime_2 = row["s2_DepartureDate"].ToString();
                    uc_1.ArrCity = row["ArrivalCityName"].ToString() + "/" + row["ArrivalAirportIATA"].ToString();
                    uc_1.ArrTime = row["s2_ReturnDate"].ToString();
                    uc_1.WaitTime = Convert.ToInt32(row["walkMinutes"].ToString());
                    uc_1.TotalTime = Convert.ToInt32(row["totalMinutes"].ToString());
                    uc_1.CabinType = cbCabin.Text;
                    if (cbCabin.SelectedValue.Equals(1)) uc_1.Price = Convert.ToDouble(row["s1_EconomyPrice"].ToString());
                    else if (cbCabin.SelectedValue.Equals(2)) uc_1.Price = Convert.ToDouble(row["s1_BusinessPrice"].ToString());
                    else if (cbCabin.SelectedValue.Equals(3)) uc_1.Price = Convert.ToDouble(row["s1_FirstPrice"].ToString());
                    uc_1.FltNum_1 = row["s1_FlightNumber"].ToString();
                    uc_1.FltNum_2 = row["s2_FlightNumber"].ToString();
                    uc_1.AvailableTickets = GetRemainingTickets(Convert.ToInt32(row["s1_ScheduleId"].ToString()));
                    uc_1.DelayRate_1 = GetDelayRateByRId(Convert.ToInt32(row["RouteId1"].ToString()));
                    uc_1.DelayRate_2 = GetDelayRateByRId(Convert.ToInt32(row["RouteId2"].ToString()));

                    panel.Controls.Add(uc_1);
                    last_top += 200;

                    /*      
                     *  DepartureCityCode TransferCityCode ArrivalCityCode  
                     *    s1_AircraftId s2_ScheduleId   s2_AircraftId 
                     * s2_EconomyPrice s2_BusinessPrice s2_FirstPrice    
                     */
                }
            }
        }

        private void initPanelFLT()
        {
            pFLTrs.Controls.Clear();
            last_top = 0;
            Label lbl = new Label();
            lbl.Text = "Departure Flights";
            lbl.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            lbl.Size = new System.Drawing.Size(215, 29);
            lbl.Location = new System.Drawing.Point(0, -2);

            pFLTrs.Controls.Add(lbl);
        }

        private void initPanelRTN()
        {
            pRTNrs.Controls.Clear();
            last_top = 0;
            Label lbl = new Label();
            lbl.Text = "Return Flights";
            lbl.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            lbl.Size = new System.Drawing.Size(215, 29);
            lbl.Location = new System.Drawing.Point(0, -2);
            pRTNrs.Controls.Add(lbl);
        }

        private void noSuchFlights(bool hasError = false)
        {
            if (hasError)
            {
                MessageBox.Show("No Such Flights.");
                initPanelFLT(); initPanelRTN();
            }
            else
            {
                if (pFLTrs.Controls.Count == 1) { MessageBox.Show("No Direct Flights."); return; }
                if (rdoRoundWay.Checked && pRTNrs.Controls.Count == 1) MessageBox.Show("No Return Flights.");
            }

        }

        private void resizeControls()
        {
            int wndWidth = this.Width; int wndHeight = this.Height;
            //Console.WriteLine(wndWidth.ToString() + ", " + wndHeight.ToString());

            int titleWidth = lblTitle.Width;
            int titleLeft = lblTitle.Location.X; int titleTop = lblTitle.Location.Y;
            lblTitle.Location = new Point(Convert.ToInt32((double)(wndWidth - titleWidth) / 2), titleTop);

            int grpWidth = groupBox1.Width;
            int grpLeft = groupBox1.Location.X; int grp1Top = groupBox1.Location.Y;
            double grpLeft_new;
            if ((grpLeft_new = wndWidth - grpWidth) > 0) groupBox1.Location = new Point(Convert.ToInt32(grpLeft_new / 2), grp1Top);

            int p1Width = pFLTrs.Width; int p1Height = pFLTrs.Height; int p1Height_min = 360;
            int p2Width = pRTNrs.Width; int p2Height = pRTNrs.Height; int p2Height_min = 300;
            if (wndWidth > p1Width) pFLTrs.Width = pRTNrs.Width = wndWidth - 100;
            double totalHeight = wndHeight - 300;
            int p1Height_new = pRTNrs.Visible ? (int)(totalHeight * 0.6) : (int)totalHeight;
            int p2Height_new = (int)(totalHeight * 0.4);
            pFLTrs.Height = p1Height_new > p1Height_min ? p1Height_new : p1Height_min;
            pRTNrs.Height = p2Height_new > p2Height_min ? p2Height_new : p2Height_min;
            pRTNrs.Location = new System.Drawing.Point(pFLTrs.Location.X, 200 + pFLTrs.Height);
            //Console.WriteLine(pFLTrs.Height.ToString() + ", " + pRTNrs.Height.ToString());
        }
        #endregion

    }
}
